using System;
using System.Data;
using System.Data.SqlClient;


namespace BusObjectCS {

   public class IBAProducts {

      private string m_DSN;

      public  IBAProducts () { }

      public  IBAProducts (string DSN) {
         m_DSN = DSN;
      }

      public string DatabaseConnection {
         set { m_DSN = value; }
         get { return m_DSN; }
      }

      public DataSet GetProducts (string productType) {
          SqlConnection sqlConnection = new SqlConnection(m_DSN);
          SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT * FROM Products WHERE ProductType='"+productType+"'", sqlConnection);

          DataSet products = new DataSet();
          sqlAdapter1.Fill(products, "products");

          return products;
      }

      public DataSet GetProductTypes () {

         SqlConnection dbConnection = new SqlConnection(m_DSN);
         dbConnection.Open();

         SqlDataAdapter sqlAdapter1 = new SqlDataAdapter("SELECT DISTINCT ProductType FROM Products", dbConnection);

         DataSet types = new DataSet();
         sqlAdapter1.Fill(types, "ProdTypes");
			
         return types;
      }

      public virtual Double AveragePrice (string productType) {
         SqlConnection dbConnection = new SqlConnection(m_DSN);
         dbConnection.Open();

         SqlDataAdapter sqlAdapter1 = new SqlDataAdapter(
         "SELECT AVG(UnitPrice) AS AveragePrice FROM Products WHERE ProductType='"+productType+"'", dbConnection);

         DataSet AvgPrice = new DataSet();
         sqlAdapter1.Fill(AvgPrice, "AveragePrice");


         DataTable priceTable; 
         priceTable = AvgPrice.Tables["AveragePrice"];
         if (priceTable.Rows.Count > 0)
         {
            return (Double)priceTable.Rows[0]["AveragePrice"];
            }
         else
            return 0;
      }
   }
}
